﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using SpotlessWeb.Helpers;
using SpotlessWeb.ViewHelpers;
using Spotless.Data;
using Spotless.Services;
using SpotlessWeb.Models;
using System.Web.Security;
using System.Net.Mail;

namespace SpotlessWeb.Controllers
{
    public class EnquiryController : Controller
    {
        public EnquiryController() : this(null)
        {
        }

        public EnquiryController(Spotless.Services.QueryService queryService)
        {
            this.QueryService = queryService ?? new Spotless.Services.QueryService();
        }   

        protected Spotless.Services.QueryService QueryService { get; private set; }
        
        // GET: /Enquiry/
        //[Authorize(Roles = "Admin")]
        [Authorize(Roles = "Admin, EnquiryManager, EnquiryCreator, QualityControl")]
        public ActionResult Index(string sort, int? page)
        {
            var filters = GetEnquiryFilters();
            ViewData.SetFilters(filters);
            var data = QueryService.GetOpenQueries(filters.ToDictionary(x => x.Value, x => Request["filter." + x.Key.DisplayName.Replace(" ", "")]), sort, page ?? 0, 20);
            return View(data);
        }

        private new Dictionary<FilterExpressionInfo, FilterExpression<Query>> GetEnquiryFilters()
        {
            var firstNameFilterInfo = FilterExpressionInfo.TextBox("First Name");
            var lastNameFilterInfo = FilterExpressionInfo.TextBox("Last Name");
            var emailFilterInfo = FilterExpressionInfo.TextBox("Email");
            var typeFilterInfo = FilterExpressionInfo.DropDown("Type", SelectListHelpers.GetSelectListFromEnumUsingValues<Spotless.Data.Query.QueryTypes>());
            var productCategoryFilterInfo = FilterExpressionInfo.DropDown("Product Category", QueryService.GetCategories().Select(x => new SelectListItem() { Text = x.Name, Value = x.CategoryId.ToString() }));
            var productNameFilterInfo = FilterExpressionInfo.TextBox("Product Name");
            var sourceFilterInfo = FilterExpressionInfo.DropDown("Source", SelectListHelpers.GetSelectListFromEnumUsingValues<Spotless.Data.Query.QuerySource>());
            var priorityFilterInfo = FilterExpressionInfo.DropDown("Priority", new[] { 1, 2, 3 }.Select(x => new SelectListItem() { Text = x.ToString(), Value = x.ToString() }));
            var dateFromFilterInfo = FilterExpressionInfo.TextBox("Date From", new { @class = "dateInput" });
            var dateToFilterInfo = FilterExpressionInfo.TextBox("Date To", new { @class = "dateInput" });

            return new Dictionary<FilterExpressionInfo, FilterExpression<Query>>{
                {firstNameFilterInfo, new FilterExpression<Query>(x => x.FirstName, FilterTypes.Contains)},
                {lastNameFilterInfo, new FilterExpression<Query>(x => x.LastName, FilterTypes.Contains)},
                {emailFilterInfo, new FilterExpression<Query>(x => x.Email, FilterTypes.Contains)},
                {typeFilterInfo, new FilterExpression<Query>(x => x.QueryTypeId, FilterTypes.EqualTo)},
                {productCategoryFilterInfo, new FilterExpression<Query>(x => x.CategoryId, FilterTypes.EqualTo)},
                {productNameFilterInfo, new FilterExpression<Query>(x => x.Product.Name, FilterTypes.Contains)},
                {sourceFilterInfo, new FilterExpression<Query>(x => x.SourceId, FilterTypes.EqualTo)},
                {priorityFilterInfo, new FilterExpression<Query>(x => x.Priority, FilterTypes.EqualTo)},
                {dateFromFilterInfo, new FilterExpression<Query>(x => x.EnquiryDate, FilterTypes.GreaterThanOrEqual)},
                {dateToFilterInfo, new FilterExpression<Query>(x => x.EnquiryDate, FilterTypes.LessThanOrEqual)}
            };
        }

        private string[] GetQueryCSVHeaders()
        {
            return new string[]{
                "Enquiry Date",
                "Name",
                "Email",
                "AddressLine 1",
                "AddressLine 2",
                "Town",
                "Post Code",
                "Country",
                "StoreId",
                "Telephone",
                "Method",
                "PaymentTypeId",
                "Amount",
                "Status",
                "Free Text",
                //"Variation",
                "Compensation Status",
                "Retailer",
                "WmManufacturere",
                "WmModel",
                "Batch Code",
                "Query Type",
                "Category",
                "Product",
                "Priority"
            };
        }

        public string[] GetQueryCSVData(Query query)
        {
            return new string[]{
                query.EnquiryDate.ToShortDateString(),
                query.LastName + (query.FirstName.HasValue() ? ", " + query.FirstName : ""),
                query.Email != null ? query.Email :"",
                query.AddressLine1 != null ? query.AddressLine1 :"",
                query.AddressLine2 != null ? query.AddressLine2 :"",
                query.Town != null ? query.Town :"",
                query.PostCode!= null ? query.PostCode:"",
                query.Country != null ? query.Country:"",
                query.StoreId != null ? query.StoreId.ToDisplayString():"",
                
                query.Telephone != null ? query.Telephone.ToDisplayString():"",

                query.Method != null ? query.Method.ToDisplayString() : "",
                query.PaymentType != null ? query.PaymentType.ToDisplayString() : "",
                query.Amount != null ? query.Amount.ToDisplayString() : "",
                query.Status.ToDisplayString(),
                query.FreeTextArea != null ? query.FreeTextArea :"",
                //query.Variation!= null ? query.Variation.ToDisplayString()  :"",
                query.CompensationStatus != null ? query.CompensationStatus.ToDisplayString()  :"",
                query.Retailer!= null ? query.Retailer.ToDisplayString() :"",
                query.WmManufacturere!= null ? query.WmManufacturere.ToDisplayString() :"",
                query.WmModel != null ? query.WmModel.ToDisplayString() :"",
                query.BatchCode != null ? query.BatchCode.ToDisplayString() :"",
                                    
                                            
                query.QueryType.ToDisplayString(),
                query.Category != null ? query.Category.Name : "",
                query.Product != null ? query.Product.Name : "",
                query.Priority.ToString()
            };
        }

        public ActionResult DownloadAllEnquires()
        {
            var data = QueryService.GetAllQueries().ToArray();

            var csv = new ActionResults.CSVActionResult();
            csv.Name = string.Format("Enquiries.csv");
            csv.ContentHeaders = GetQueryCSVHeaders();

            csv.Rows = data.Select(x => GetQueryCSVData(x)).ToList();

            return csv;

        }

        public ActionResult DownloadEnqiries()
        {
            var filters = GetEnquiryFilters();
            var data = QueryService
                .GetOpenQueries(filters.ToDictionary(x => x.Value, x => Request["filter." + x.Key.DisplayName.Replace(" ", "")]))
                .OrderBy(x => x.EnquiryDate).ToArray();

            var csv = new ActionResults.CSVActionResult();
            csv.Name = string.Format("Enquiries.csv");
            csv.ContentHeaders = GetQueryCSVHeaders();

            csv.Rows = data.Select(x => GetQueryCSVData(x)).ToList();

            return csv;
        }

        public ActionResult DownloadCompletedEnqiries()
        {
            var filters = GetEnquiryFilters();
            var data = QueryService
                .GetCompletedQueries(filters.ToDictionary(x => x.Value, x => Request["filter." + x.Key.DisplayName.Replace(" ", "")]))
                .OrderBy(x => x.EnquiryDate).ToArray();
            var csv = new ActionResults.CSVActionResult();
            csv.Name = string.Format("FilteredCompletedEnquiries.csv");
            csv.ContentHeaders = GetQueryCSVHeaders();
            csv.Rows = data.Select(x => GetQueryCSVData(x)).ToList();
            return csv;
        }

        public ActionResult DownloadApprovedCompensation()
        {
            var filters = GetEnquiryFilters();
            var data = QueryService
                .GetOpenApprovedCompensation(filters.ToDictionary(x => x.Value, x => Request["filter." + x.Key.DisplayName.Replace(" ", "")]))
                .OrderBy(x => x.EnquiryDate).ToArray();
            var csv = new ActionResults.CSVActionResult();
            csv.Name = string.Format("FilteredApprovedCompensation.csv");
            //csv.ContentHeaders = GetQueryCSVHeaders();
            //csv.Rows = data.Select(x => GetQueryCSVData(x)).ToList();

            csv.ContentHeaders = new[] {
                "Complaint Number",
                "Date",
                "Name",
                "Email",
                "AddressLine 1",
                "AddressLine 2",
                "Town",
                "Post Code",
                "Country",
                "Amount",
                "Free Text",
                "Category",
                "Product"

};
            csv.Rows = new List<string[]>();

            foreach (var item in data) {
                csv.Rows.Add(new[] {
                    item.QueryId.ToString(),
                    item.EnquiryDate.ToShortDateString(),
                    item.LastName + (item.FirstName.HasValue() ? ", " + item.FirstName : ""),
                    item.Email != null ? item.Email :"",
                    item.AddressLine1 != null ? item.AddressLine1 :"",
                    item.AddressLine2 != null ? item.AddressLine2 :"",
                    item.Town != null ? item.Town :"",
                    item.PostCode!= null ? item.PostCode:"",
                    item.Country != null ? item.Country:"",
                    item.Amount != null ? item.Amount.ToDisplayString() : "",
                    item.FreeTextArea != null ? item.FreeTextArea :"",
                    item.Category != null ? item.Category.Name : "",
                    item.Product != null ? item.Product.Name : "",

                });
            }

            return csv;
        }


        
        //public ActionResult Approved(string sort, int? page)
        //{
        //    var filters = GetEnquiryFilters();
        //    ViewData.SetFilters(filters);
        //    var data = QueryService.GetOpenApprovedCompensation(filters.ToDictionary(x => x.Value, x => Request["filter." + x.Key.DisplayName.Replace(" ", "")]), sort, page ?? 0, 20);
        //    return View(data);
        //}
        // GET: /Enquiry/Details/5
        [Authorize(Roles = "Admin, EnquiryManager, EnquiryCreator, QualityControl")]
        public ActionResult Details(int id)
        {
            var data = QueryService.GetById(id);
            return View(data);
        }

        // GET: /Enquiry/Create
        [Authorize(Roles = "Admin, EnquiryCreator")]
        public ActionResult Create()
        {
            ViewData.AddCategories(QueryService.GetCategories());
            return View();  
        }

        private int? GetIntValue(string key, FormCollection collection)
        {
            int val = 0;
            if(int.TryParse(collection[key], out val))
                return val;
            else 
                return null;
        }

        private void SetValuesFromDrillDowns(Query query, FormCollection collection)
        {
            //set drill down values manually
            if (collection["Query.CategoryId"] != null)
            {
                query.CategoryId = GetIntValue("Query.CategoryId", collection) ?? 0;
                if (query.CategoryId == 0)
                    ModelState.AddModelError("Query.CategoryId", "Category must be selected");
            }

            if (ModelState.IsValid && collection["Query.ProductId"] != null)
            {
                query.ProductId = GetIntValue("Query.ProductId", collection);
                query.Product = EnsureDrillDownLoaded(query.ProductId, query.Product);
                if (query.Product == null || query.Product.Children.Count(x => x.Type == Spotless.Data.DrillDown.DrillDownTypes.Product) > 0)
                {
                    ModelState.AddModelError("Query.ProductId", "Product must be selected");
                }
            }
            else
            {
                query.ProductId = null;
                query.Product = null;
            }

            if (ModelState.IsValid && collection["Query.VariationId"] != null)
            {
                query.VariationId = GetIntValue("Query.VariationId", collection);
                query.Variation = EnsureDrillDownLoaded(query.VariationId, query.Variation);
                if (query.Variation == null && query.Product != null && query.Product.Children.Count(x => x.Type == Spotless.Data.DrillDown.DrillDownTypes.Variation) > 0)
                {
                    ModelState.AddModelError("Query.VariationId", "Variation must be selected");
                }
                else if (query.Variation != null && query.Variation.Children.Count(x => x.Type == Spotless.Data.DrillDown.DrillDownTypes.Variation) > 0)
                {
                    ModelState.AddModelError("Query.VariationId", "Variation must be selected");
                }
            }
            else
            {
                query.VariationId = null;
            }

            if (ModelState.IsValid && collection["Query.QueryType"] != null)
            {
                if (collection["Query.QueryType"] != "")
                {
                    try
                    {
                        query.QueryType = (Query.QueryTypes)Enum.Parse(typeof(Query.QueryTypes), collection["Query.QueryType"]);
                    }
                    catch (FormatException)
                    {
                        query.QueryType = null;
                    }
                }
                else
                {
                    query.QueryType = null;
                }
                if (query.QueryType == null)
                {
                    ModelState.AddModelError("Query.QueryType", "Query Type must be selected");
                }
            }
            else
            {
                query.QueryType = null;
            }


            if (ModelState.IsValid && collection["Query.QuerySelectionId"] != null)
            {
                query.QuerySelectionId = GetIntValue("Query.QuerySelectionId", collection);
                query.QuerySelection = EnsureDrillDownLoaded(query.QuerySelectionId, query.QuerySelection);
                if (query.QuerySelection == null || query.QuerySelection.Children.Count() > 0)
                {
                    ModelState.AddModelError("Query.QuerySelectionId", string.Format("{0} Type must be selected", query.QueryType.Value.ToDisplayString()));
                }
            }
            else
            {
                query.QuerySelectionId = null;
                query.QuerySelection = null;
            }
        }

        //
        // POST: /Enquiry/Create
        [HttpPost]
        [Authorize(Roles = "Admin, EnquiryCreator")]
        public ActionResult Create(FormCollection collection)
        {
            var query = QueryService.CreateQuery();

            SetValuesFromDrillDowns(query, collection);
            
            //set the compensationStatusId to pending(1) if payment type is compensation (3) 

            //byte compensationId= 3;
            //byte paymentTypeId = 0;
            //byte.TryParse(collection["PaymentType"], out paymentTypeId);
            //if (paymentTypeId == 3)
            //    query.CompensationStatus = (Query.CompensationStatusTypes)compensationId;
           
            TryUpdateModel(query, new string[]{
                "FirstName",
                "LastName",
                "Email",
                "AddressLine1",
                "AddressLine2",
                "Town",
                "County",
                "PostCode",
                "Country",
                "Source",
                "Retailer",
                "EnquiryDate",
                "Priority",
                "WmManufacturere",
                "WmModel",
                "Status",
                "Method",
                "PaymentType",
                "Amount",
                "FreeTextArea"});

            if (ModelState.IsValid)
            {
                QueryService.SaveChanges();
                
                if (query.PaymentType.HasValue && query.PaymentType.Value == Query.PaymentTypes.Compensation)
                {
                    SendEmail(query);
                }

                if (query.ProductId.HasValue && query.QueryType.Value == Query.QueryTypes.Complaint )
                {
                    SendComplaintAlertEmail(query);
                }
                
                return RedirectToAction("Index");
            }

            ViewData.AddCategories(QueryService.GetCategories());
            return View(query);
        }

        // GET: /Enquiry/Create
       // [Authorize(Roles = "Admin, EnquiryManager")]
        public ActionResult ContactForm(string id)
        {
            var siteName = id;
            ViewData["sid"] = siteName;
            
            return View();
        }

        //contactform start
        // POST: /Enquiry/Create
        [HttpPost]
        public ActionResult ContactForm(FormCollection collection)
        {
            var query = QueryService.CreateQuery();

            //SetValuesFromDrillDowns(query, collection);


            TryUpdateModel(query, new string[]{
                "CategoryId",
                "ProductId",
                "FirstName",
                "LastName",
                "Email",
                "AddressLine1",
                "AddressLine2",
                "Town",
                "County",
                "PostCode",
                "Country",
                "Source",
                    //"Retailer",
                "EnquiryDate",
                "Priority",

                "QueryTypeId",

                
                    //"WmManufacturere",
                    //"WmModel",
                "Status",
                //"Method",
                //"PaymentType",
                //"Amount",
                "FreeTextArea"
            });

            if (ModelState.IsValid)
            {
                QueryService.SaveChanges();
                //if (query.PaymentType.HasValue && query.PaymentType.Value == Query.PaymentTypes.Compensation)
                //{
                //    SendEmail(query);
                //}
                
                //thank you page?
                return RedirectToAction("ContactFormThanks");
            }

            //ViewData.AddCategories(QueryService.GetCategories());
            return View(query);
        }
        //contact form end


        public ActionResult ContactFormThanks()
        {
            
            return View();
        }


        //GET: /Enquiry/Completed
        //
        [Authorize(Roles = "Admin, EnquiryManager,EnquiryCreator, QualityControl")]
        public ActionResult Completed(string sort, int? page)
        {
            var filters = GetEnquiryFilters();
            ViewData.SetFilters(filters);
            var data = QueryService.GetCompletedQueries(filters.ToDictionary(x => x.Value, x => Request["filter." + x.Key.DisplayName.Replace(" ", "")]), sort, page ?? 0, 20);
            //var data = QueryService.GetOpenQueries(filters.ToDictionary(x => x.Value, x => Request[ViewData.GetFilterKey(x.Value)]), sort, page ?? 0, 5);
            return View(data);
            //////
        }
        
        // GET: /Enquiry/Edit/5
 
        public ActionResult Edit(int id)
        {
            var data = QueryService.GetById(id);
            return View(data);
        }

        // POST: /Enquiry/Edit/5
        [HttpPost]
        [Authorize(Roles = "Admin, EnquiryManager, EnquiryCreator")]
        public ActionResult Edit(int id, FormCollection collection)
        {

            var query = QueryService.GetById(id);

            var originalStatus = query.PaymentType;

            SetValuesFromDrillDowns(query, collection);

            TryUpdateModel(query);

            if (ModelState.IsValid)
            {
                QueryService.SaveChanges();
                if (query.PaymentType.HasValue && query.PaymentType.Value == Query.PaymentTypes.Compensation)
                {
                    if (!originalStatus.HasValue || originalStatus.Value != Query.PaymentTypes.Compensation)
                    {
                        SendEmail(query);

                    }
                }
                return RedirectToAction("Index");
            }
            return View(query);
            //try
            //{
                // TODO: Add update logic here
 
             //   return RedirectToAction("Index");
            //}
            //catch
           // {
            //    return View();
            //}
        }

        private void SendEmail(Query query)
        {
            //send email
            MailMessage mail = new MailMessage();
            foreach (var username in query.Product.BrandManagerProducts.Select(x => x.aspnet_User.UserName))
            {
                var member = Membership.GetUser(username);
                if (member != null)
                {
                    mail.Bcc.Add(new MailAddress(member.Email));
                }
            }
            if (mail.Bcc.Count > 0)
            {
                mail.From = new MailAddress("spotless@web.com");
                mail.Subject = "Compensation Request for " + query.Product.Name;
                mail.Body = "There is a compensation request please log into control panel.\r\n"+"http://"+Request.Url.Host + "/Enquiry/Pending";

                System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient();
                client.Send(mail);
                mail.Dispose();
            }
        }


        private void SendComplaintAlertEmail(Query query) {
            //send email
            MailMessage mail = new MailMessage();
            
            //Olly CHECK THIS
            foreach (var username in query.Product.ComplaintsAlert.Select(x => x.aspnet_User.UserName))
            {
                var member = Membership.GetUser(username);
                if (member != null)
                {
                    mail.Bcc.Add(new MailAddress(member.Email));
                }
            }
            if (mail.Bcc.Count > 0)
            {
                mail.From = new MailAddress("spotless@web.com");
                mail.Subject = "Complaint received for "+ query.Product.Name;
                mail.Body = "A complaint has been received for a product you are registered with.\r\n" + "http://" + Request.Url.Host + "/Enquiry/Details/" + query.QueryId;

                System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient();
                client.Send(mail);
                mail.Dispose();
            }
        }


        // GET: /Enquiry/Delete/5
        public ActionResult Delete(int id)
        {
            return View();
        }

        // POST: /Enquiry/Delete/5
        
        [HttpPost]
        [Authorize(Roles = "Admin, EnquiryCreator")]
        public ActionResult Delete(int id, FormCollection collection)
        {
            try
            {
                // TODO: Add delete logic here
 
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        public ActionResult DrillDown(int? id, FormCollection collection)
        {
            var data = new Models.QueryDrillDownViewModel();
            data.AllowEdit = true;
            if (id.HasValue && id.Value > 0)
            {
                data.Query = QueryService.GetById(id.Value);
            }
            if (data.Query == null)
            {
                data.Query = new Query();
            }
            data.DrillDowns = new DrillDown[0];
            if(collection.AllKeys.Count() > 0)
                SetValuesFromDrillDowns(data.Query, collection);

            if (data.Query.CategoryId > 0 && (data.Query.Category == null || data.Query.CategoryId != data.Query.Category.CategoryId))
            {
                data.Query.Category = QueryService.GetCategoryById(data.Query.CategoryId);
            }

            if (data.Query.Category == null)
            {
                data.Categories = QueryService.GetCategories().ToArray();
            }
            else
            {
                data.Query.Product = EnsureDrillDownLoaded(data.Query.ProductId, data.Query.Product);

                if (data.Query.Product == null)
                {
                    data.DrillDowns = QueryService.GetTopLevelDrillDown(data.Query.CategoryId, Spotless.Data.DrillDown.DrillDownTypes.Product).ToArray();
                }
                else
                {
                    data.DrillDowns = QueryService.GetDrillDownsByParentId(data.Query.ProductId.Value, Spotless.Data.DrillDown.DrillDownTypes.Product).ToArray();
                }

                if (data.DrillDowns.Length == 0)
                {
                    data.Query.Variation = EnsureDrillDownLoaded(data.Query.VariationId, data.Query.Variation);
                    if (data.Query.VariationId == null || data.Query.VariationId.Value == 0)
                    {
                        //get product variations
                        data.DrillDowns = QueryService.GetDrillDownsByParentId(data.Query.ProductId.Value, Spotless.Data.DrillDown.DrillDownTypes.Variation).ToArray();
                    }
                    else
                    {
                        data.DrillDowns = QueryService.GetDrillDownsByParentId(data.Query.VariationId.Value, Spotless.Data.DrillDown.DrillDownTypes.Variation).ToArray();
                    }
                }

                if (data.DrillDowns.Length == 0 && data.Query.QueryType != null)
                {
                    data.Query.QuerySelection = EnsureDrillDownLoaded(data.Query.QuerySelectionId, data.Query.QuerySelection);
                    if (data.Query.QuerySelectionId == null)
                    {
                        var drillDownType = data.Query.QueryType.Value == Query.QueryTypes.Complaint ? Spotless.Data.DrillDown.DrillDownTypes.Complaint : Spotless.Data.DrillDown.DrillDownTypes.Enquiry;
                        data.DrillDowns = QueryService.GetDrillDownsByParentId(data.Query.ProductId.Value, drillDownType).ToArray();
                        if(data.DrillDowns.Length == 0)
                            data.DrillDowns = QueryService.GetTopLevelDrillDown(data.Query.CategoryId, drillDownType).ToArray();
                    }
                    else
                    {
                        data.DrillDowns = QueryService.GetDrillDownsByParentId(data.Query.QuerySelection.DrillDownId, data.Query.QuerySelection.Type).ToArray();
                    }
                }

                /*if (data.DrillDowns.Length == 0 && data.Query.QueryType.HasValue)
                {
                    if (data.Query.QuerySelection.Name.ToLower() == "damage" && (data.Query.Product.Name.ToLower() == "oust" || data.Query.Category.Name.ToLower() == "fabric care" || data.Query.Category.Name.ToLower() == "fabric dyes"))
                    {
                        if (!data.Query.WmManufacturereId.HasValue)
                        {
                            data.Manufactures = SelectListHelpers.GetSelectListFromEnum<Query.WmManufacturers>();
                        }
                    }
                }*/
            }

            return PartialView("Partial/DrillDown", data);
        }

        private DrillDown EnsureDrillDownLoaded(int? id, DrillDown item)
        {
            if (id > 0 && (item == null || id != item.DrillDownId))
            {
                return QueryService.GetDrillDownById(id.Value);
            }
            else
            {
                return item;
            }
        }


        // GET: /Pending/
        [Authorize(Roles = "Admin, EnquiryManager")]
        public ActionResult Pending(string sort, int? page)
        {
            //var data = QueryService.GetPendingCompensationForUser(User.Identity);
            //var data = QueryService.GetPendingCompensation();
            //return View(data);

            /////////
            var firstNameFilterInfo = FilterExpressionInfo.TextBox("First Name");
            var lastNameFilterInfo = FilterExpressionInfo.TextBox("Last Name");
            var emailFilterInfo = FilterExpressionInfo.TextBox("Email");
            var typeFilterInfo = FilterExpressionInfo.DropDown("Type", SelectListHelpers.GetSelectListFromEnumUsingValues<Spotless.Data.Query.QueryTypes>());
            var productCategoryFilterInfo = FilterExpressionInfo.DropDown("Product Category", QueryService.GetCategories().Select(x => new SelectListItem() { Text = x.Name, Value = x.CategoryId.ToString() }));
            var sourceFilterInfo = FilterExpressionInfo.DropDown("Source", SelectListHelpers.GetSelectListFromEnumUsingValues<Spotless.Data.Query.QuerySource>());
            var priorityFilterInfo = FilterExpressionInfo.DropDown("Priority", new[] { 1, 2, 3 }.Select(x => new SelectListItem() { Text = x.ToString(), Value = x.ToString() }));
            var dateFromFilterInfo = FilterExpressionInfo.TextBox("Date From", new { @class = "dateInput" });
            var dateToFilterInfo = FilterExpressionInfo.TextBox("Date To", new { @class = "dateInput" });

            var filters = new Dictionary<FilterExpressionInfo, FilterExpression<Query>>{
                {firstNameFilterInfo, 
                    new FilterExpression<Query>(x => x.FirstName, FilterTypes.Contains)},
                {lastNameFilterInfo, 
                    new FilterExpression<Query>(x => x.LastName, FilterTypes.Contains)},
                {emailFilterInfo, 
                    new FilterExpression<Query>(x => x.Email, FilterTypes.Contains)},
                {typeFilterInfo, 
                    new FilterExpression<Query>(x => x.QueryTypeId, FilterTypes.EqualTo)},
                {productCategoryFilterInfo, 
                    new FilterExpression<Query>(x => x.CategoryId, FilterTypes.EqualTo)},
                {sourceFilterInfo, 
                    new FilterExpression<Query>(x => x.SourceId, FilterTypes.EqualTo)},
                {priorityFilterInfo, 
                    new FilterExpression<Query>(x => x.Priority, FilterTypes.EqualTo)},
                {dateFromFilterInfo, 
                    new FilterExpression<Query>(x => x.EnquiryDate, FilterTypes.GreaterThanOrEqual)},
                {dateToFilterInfo, 
                    new FilterExpression<Query>(x => x.EnquiryDate, FilterTypes.LessThanOrEqual)}
            };

            ViewData.SetFilters(filters);
            
            var data = QueryService.GetPendingCompensationForUser(User.Identity, filters.ToDictionary(x => x.Value, x => Request[ViewData.GetFilterKey(x.Value)]), sort, page ?? 0, 5);

            return View(data);
            /////////
        }

        // GET: /Approve/
        [Authorize(Roles = "Admin, EnquiryManager")]
        public ActionResult Approve(int id)
        {
            var query = QueryService.GetById(id);
            //query.CompensationStatus = (Query.CompensationStatusTypes)compStat;
            query.CompensationStatus = Query.CompensationStatusTypes.Confirm;
            QueryService.SaveChanges();
            return RedirectToAction("Pending");
        }

        // GET: /Decline/
        [Authorize(Roles = "Admin, EnquiryManager")]
        public ActionResult Decline(int id)
        {
            var query = QueryService.GetById(id);
            //query.CompensationStatus = (Query.CompensationStatusTypes)compStat;
            query.CompensationStatus = Query.CompensationStatusTypes.Reject;
            QueryService.SaveChanges();
            return RedirectToAction("Pending");
        }

        // GET: /Approved/

        [Authorize(Roles = "Admin, EnquiryManager, EnquiryCreator")]
        public ActionResult Approved(string sort, int? page)
        {
            var filters = GetEnquiryFilters();
            ViewData.SetFilters(filters);
            var data = QueryService.GetOpenApprovedCompensation(filters.ToDictionary(x => x.Value, x => Request["filter." + x.Key.DisplayName.Replace(" ", "")]), sort, page ?? 0, 20);
            return View(data);
        }

        // GET: /Declined/
        [Authorize(Roles = "Admin, EnquiryManager, EnquiryCreator")]
        public ActionResult Declined(string sort, int? page)
        {
            var filters = GetEnquiryFilters();
            ViewData.SetFilters(filters);
            var data = QueryService.GetOpenDeclinedCompensation(filters.ToDictionary(x => x.Value, x => Request["filter." + x.Key.DisplayName.Replace(" ", "")]), sort, page ?? 0, 20);
            
            return View(data);
            //////
            


            //var data = QueryService.GetOpenApprovedCompensation();
            //return View(data);
        }


        //public ActionResult Reports()
        //{
        //    var data = QueryService.countQueries();
        //    return View(data);
        //}



        // GET: /Enquiry/Edit/5
        [Authorize(Roles = "Admin, EnquiryManager, EnquiryCreator, QualityControl")]
        public ActionResult Report(string from, string to)
        {
            DateTime? from2 = null;
            DateTime? to2 = null;
            if (from.HasValue())
            {
                try
                {
                    from2 = DateTime.Parse(from);
                }
                catch (FormatException) { }
            }
            if (to.HasValue())
            {
                try
                {
                    to2 = DateTime.Parse(to);
                }
                catch (FormatException) { }
            }

            //var data = QueryService.countQueries();
            
            var count = QueryService.CompletedQueries_GetCount(from2, to2);
            var countEnquiry = QueryService.CompletedQueries_GetEnquiryCount(from2, to2);
            var countComplaint = count - countEnquiry;
            var total = QueryService.CompletedQueries_GetTotalAmount(from2, to2);

            var countWebsiteEnquiry= QueryService.CompletedQueries_GetWebsiteEnquiryCount(from2, to2);
            var countWebsiteComplaint= QueryService.CompletedQueries_GetWebsiteComplaintCount(from2, to2);
            var countWebsiteCompensation= QueryService.CompletedQueries_GetWebsiteTotalAmount(from2, to2);

            var countEmailEnquiry = QueryService.CompletedQueries_GetEmailEnquiryCount(from2, to2);
            var countEmailComplaint = QueryService.CompletedQueries_GetEmailComplaintCount(from2, to2);
            var countEmailCompensation = QueryService.CompletedQueries_GetEmailTotalAmount(from2, to2);

            var countTelephoneEnquiry = QueryService.CompletedQueries_GetTelephoneEnquiryCount(from2, to2);
            var countTelephoneComplaint = QueryService.CompletedQueries_GetTelephoneComplaintCount(from2, to2);
            var countTelephoneCompensation = QueryService.CompletedQueries_GetTelephoneTotalAmount(from2, to2);

            var countLetterEnquiry = QueryService.CompletedQueries_GetLetterEnquiryCount(from2, to2);
            var countLetterComplaint = QueryService.CompletedQueries_GetLetterComplaintCount(from2, to2);
            var countLetterCompensation = QueryService.CompletedQueries_GetLetterTotalAmount(from2, to2);

            var countRetailerEnquiry = QueryService.CompletedQueries_GetRetailerEnquiryCount(from2, to2);
            var countRetailerComplaint = QueryService.CompletedQueries_GetRetailerComplaintCount(from2, to2);
            var countRetailerCompensation = QueryService.CompletedQueries_GetRetailerTotalAmount(from2, to2);
            
            return View(new Models.EnquiryBasicReportViewModel(from2, to2, count, countEnquiry, countComplaint, total,
                countWebsiteEnquiry, countWebsiteComplaint, countWebsiteCompensation,
                countEmailEnquiry, countEmailComplaint, countEmailCompensation,
                countTelephoneEnquiry, countTelephoneComplaint, countTelephoneCompensation,
                countLetterEnquiry, countLetterComplaint, countLetterCompensation,
                countRetailerEnquiry, countRetailerComplaint, countRetailerCompensation
                
                ));
            //return View(data);
        }

        public ActionResult ReportDownload(string from, string to)
        {
            DateTime? from2 = null;
            DateTime? to2 = null;
            if (from.HasValue())
            {
                try
                {
                    from2 = DateTime.Parse(from);
                }
                catch (FormatException) { }
            }
            if (to.HasValue())
            {
                try
                {
                    to2 = DateTime.Parse(to);
                }
                catch (FormatException) { }
            }

            //var data = QueryService.countQueries();
            var count = QueryService.CompletedQueries_GetCount(from2, to2);
            var countEnquiry = QueryService.CompletedQueries_GetEnquiryCount(from2, to2);
            var countComplaint = count - countEnquiry;
            var total = QueryService.CompletedQueries_GetTotalAmount(from2, to2);

            var countWebsiteEnquiry = QueryService.CompletedQueries_GetWebsiteEnquiryCount(from2, to2);
            var countWebsiteComplaint = QueryService.CompletedQueries_GetWebsiteComplaintCount(from2, to2);
            var countWebsiteTotal = countWebsiteEnquiry + countWebsiteComplaint;
            var countWebsiteCompensation = QueryService.CompletedQueries_GetWebsiteTotalAmount(from2, to2);

            var countEmailEnquiry = QueryService.CompletedQueries_GetEmailEnquiryCount(from2, to2);
            var countEmailComplaint = QueryService.CompletedQueries_GetEmailComplaintCount(from2, to2);
            var countEmailTotal = countEmailEnquiry + countEmailComplaint; 
            var countEmailCompensation = QueryService.CompletedQueries_GetEmailTotalAmount(from2, to2);

            var countTelephoneEnquiry = QueryService.CompletedQueries_GetTelephoneEnquiryCount(from2, to2);
            var countTelephoneComplaint = QueryService.CompletedQueries_GetTelephoneComplaintCount(from2, to2);
            var countTelephoneTotal = countTelephoneEnquiry + countTelephoneComplaint; 
            var countTelephoneCompensation = QueryService.CompletedQueries_GetTelephoneTotalAmount(from2, to2);

            var countLetterEnquiry = QueryService.CompletedQueries_GetLetterEnquiryCount(from2, to2);
            var countLetterComplaint = QueryService.CompletedQueries_GetLetterComplaintCount(from2, to2);
            var countLetterTotal= countLetterEnquiry + countLetterComplaint ;
            var countLetterCompensation = QueryService.CompletedQueries_GetLetterTotalAmount(from2, to2);

            var countRetailerEnquiry = QueryService.CompletedQueries_GetRetailerEnquiryCount(from2, to2);
            var countRetailerComplaint = QueryService.CompletedQueries_GetRetailerComplaintCount(from2, to2);
            var countRetailerTotal = countRetailerEnquiry + countRetailerComplaint;
            var countRetailerCompensation = QueryService.CompletedQueries_GetRetailerTotalAmount(from2, to2);
            //
            //return View(new Models.EnquiryBasicReportViewModel(from2, to2, count, countEnquiry, countComplaint, total,
            //    countWebsiteEnquiry, countWebsiteComplaint, countWebsiteCompensation,
            //    countEmailEnquiry, countEmailComplaint, countEmailCompensation,
            //    countTelephoneEnquiry, countTelephoneComplaint, countTelephoneCompensation,
            //    countLetterEnquiry, countLetterComplaint, countLetterCompensation,
            //    countRetailerEnquiry, countRetailerComplaint, countRetailerCompensation

            //    ));

            //


            var csv = new ActionResults.CSVActionResult();
            csv.Name = string.Format("Report.csv");

            if (!string.IsNullOrEmpty(from) || !string.IsNullOrEmpty(to))
            {
                csv.Name = string.Format("Basic_Report_" + from + "-" + to + ".csv");
            }
            else
            {
                csv.Name = string.Format("Basic_Report.csv");
            }


            //, "Website", "Email", "Phone", "Letter" 
            csv.ContentHeaders = new[] { "Source", "Enquiry", "Complaint", "Total", "Cost"};
            
            //csv.Rows = data.Select(x => GetQueryCSVData(x)).ToList();
            csv.Rows = new List<string[]>();
            csv.Rows.Add(new[] {
                            "Total",
                            count.ToString(),
                            countEnquiry.ToString(),
                            countComplaint.ToString(),
                            total.ToString()
                        });
            csv.Rows.Add(new[] {
                            "Website",
                            countWebsiteEnquiry.ToString(),
                            countWebsiteComplaint.ToString(),
                            countWebsiteTotal.ToString(),
                            countWebsiteCompensation.ToString()
                });

            csv.Rows.Add(new[] {
                            "Email",
                            countEmailEnquiry.ToString(),
                            countEmailComplaint.ToString(),
                            countEmailTotal.ToString(),
                            countEmailCompensation.ToString()
                });
            csv.Rows.Add(new[] {
                            "Telephone  ",
                            countTelephoneEnquiry.ToString(),
                            countTelephoneComplaint.ToString(),
                            countTelephoneTotal.ToString(),
                            countTelephoneCompensation.ToString(),
                            
                });
            csv.Rows.Add(new[] {
                            "Letter",
                            countLetterEnquiry.ToString(),
                            countLetterComplaint .ToString(),
                            countLetterTotal.ToString(),
                            countLetterCompensation.ToString(),
                            
                        });
            csv.Rows.Add(new[] {
                            "Retailer",
                            countRetailerEnquiry.ToString(),
                            countRetailerComplaint.ToString(),
                            countRetailerTotal.ToString(),
                            countRetailerCompensation.ToString(),
                        });

            return csv;
        }

        //// POST: /Enquiry/Edit/5
        //[HttpPost]
        //[Authorize(Roles = "Admin, EnquiryManager, EnquiryCreator")]
        //public ActionResult Reports()
        //{
        //    return View();
        //}

        [Authorize(Roles = "Admin, EnquiryManager, EnquiryCreator")]
        public ActionResult DetailedReport(string from, string to, List<int> categoryIDs, List<int> productIDs, bool? isCurrentMonth, bool? isLastMonth, bool? doSearch)
        {
            var data = GetDetailedReportViewModel(@from, to, categoryIDs, productIDs, isCurrentMonth, isLastMonth);
            if (doSearch ?? false)
            {
                data.ReportData = QueryService.GetDetailedReport(data.From, data.To, data.CategoryIDs.ToArray(), data.ProductIDs.ToArray());
            }
            return View(data);
        }

        private DetailedReportViewModel GetDetailedReportViewModel(string from, string to, List<int> categoryIDs, List<int> productIDs,
                                                                   bool? isCurrentMonth, bool? isLastMonth)
        {
            var data = new Models.DetailedReportViewModel();
            DateTime tmpFrom, tmpTo = DateTime.MinValue;
            if (DateTime.TryParse(@from, out tmpFrom))
                data.From = tmpFrom;
            if (DateTime.TryParse(to, out tmpTo))
                data.To = tmpTo;

            data.Categories = QueryService.GetCategories()
                                          .ToDictionary(x => x,
                                                        x =>
                                                        QueryService.GetTopLevelDrillDown(x.CategoryId,
                                                                                          Spotless.Data.DrillDown.DrillDownTypes
                                                                                                  .Product));
            data.CategoryIDs = categoryIDs ?? new List<int>();

            if (!data.From.HasValue)
            {
                if (isCurrentMonth ?? false)
                {
                    data.From = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
                }
                else if (isLastMonth ?? false)
                {
                    data.From = new DateTime(DateTime.Today.AddMonths(-1).Year, DateTime.Today.AddMonths(-1).Month, 1);
                }
            }
            if (!data.To.HasValue && data.From.HasValue)
            {
                data.To = data.From.Value.AddMonths(1);
            }

            data.ProductIDs = productIDs ?? new List<int>();
            return data;
        }

        [Authorize(Roles = "Admin, EnquiryManager, EnquiryCreator")]
        public ActionResult DetailedReportDownload(string from, string to, List<int> categoryIDs, List<int> productIDs, bool? isCurrentMonth, bool? isLastMonth)
        {
            var data = GetDetailedReportViewModel(from, to, categoryIDs, productIDs, isCurrentMonth, isLastMonth);
            /*var data = new Models.DetailedReportViewModel();
            DateTime tmpFrom, tmpTo = DateTime.MinValue;
            if (DateTime.TryParse(from, out tmpFrom))
                data.From = tmpFrom;
            if (DateTime.TryParse(to, out tmpTo))
                data.To = tmpTo;
            data.CategoryIDs = categoryIDs ?? new List<int>();
            data.ProductIDs = productIDs ?? new List<int>();*/
            data.ReportData = QueryService.GetDetailedReport(data.From, data.To, data.CategoryIDs.ToArray(), data.ProductIDs.ToArray());
            
            var csv = new ActionResults.CSVActionResult();
            if (data.From.HasValue && data.To.HasValue)
            {
                csv.Name = string.Format("DetailedReport_{0:yyyyMMdd}-{1:yyyyMMdd}.csv", data.From.Value, data.To.Value);
            }
            else if (data.From.HasValue)
            {
                csv.Name = string.Format("DetailedReport_From_{0:yyyyMMdd}.csv", data.From.Value);
            }
            else if (data.To.HasValue)
            {
                csv.Name = string.Format("DetailedReport_To_{0:yyyyMMdd}.csv", data.To.Value);
            }
            else
            {
                csv.Name = string.Format("DetailedReport.csv");
            }
            

            csv.ContentHeaders = new[] { "Category", "Product", "Variant", "Source", "Total", "Cost", "Website", "Email", "Phone", "Letter"};
            csv.Rows = new List<string[]>();
            

            foreach (var categoryGroup in data.ReportData.GroupBy(x => new { name = x.Category ?? "N/A", val = x.CategoryID }))
            {
                if (data.CategoryIDs.Count != 0){
                    csv.Rows.Add(new[] {
                        categoryGroup.Key.name,
                        "",
                        "",
                        "",
                        categoryGroup.Count().ToString(),
                        categoryGroup.Sum(x => x.Amount).ToString(),
                        categoryGroup.Count(x => x.SourceID == 1).ToString(),
                        categoryGroup.Count(x => x.SourceID == 2).ToString(),
                        categoryGroup.Count(x => x.SourceID == 3).ToString(),
                        categoryGroup.Count(x => x.SourceID == 4).ToString()
                    });
                    
                    var categoryGroupEnquires = categoryGroup.Where(x => x.QueryTypeID == (byte)Spotless.Data.Query.QueryTypes.Enquiry);
                        csv.Rows.Add(new[] {
                            "",
                            "",
                            "",
                            "Enquiries Total",
                            categoryGroupEnquires.Count().ToString(),
                            categoryGroupEnquires.Sum(x => x.Amount).ToString(),
                            categoryGroupEnquires.Count(x => x.SourceID == 1).ToString(),
                            categoryGroupEnquires.Count(x => x.SourceID == 2).ToString(),
                            categoryGroupEnquires.Count(x => x.SourceID == 3).ToString(),
                            categoryGroupEnquires.Count(x => x.SourceID == 4).ToString()
                        });

                    var categorygroupComplaints = categoryGroup.Where(x => x.QueryTypeID == (byte)Spotless.Data.Query.QueryTypes.Complaint);
                    csv.Rows.Add(new[] {
                            "",
                            "",
                            "",
                            "Complaints Total",
                            categorygroupComplaints.Count().ToString(),
                            categorygroupComplaints.Sum(x => x.Amount).ToString(),
                            categorygroupComplaints.Count(x => x.SourceID == 1).ToString(),
                            categorygroupComplaints.Count(x => x.SourceID == 2).ToString(),
                            categorygroupComplaints.Count(x => x.SourceID == 3).ToString(),
                            categorygroupComplaints.Count(x => x.SourceID == 4).ToString()
                        });


                    foreach (var categoryGroupSelectionGroup in categorygroupComplaints.GroupBy(x => new { name = x.Selection, val = x.SelectionID }))
                    {
                        csv.Rows.Add(new[] {
                            "",
                            "",
                            "",
                            categoryGroupSelectionGroup.Key.name ?? "Other",
                            categoryGroupSelectionGroup.Count().ToString(),
                            categoryGroupSelectionGroup.Sum(x => x.Amount).ToString(),
                            categoryGroupSelectionGroup.Count(x => x.SourceID == 1).ToString(),
                            categoryGroupSelectionGroup.Count(x => x.SourceID == 2).ToString(),
                            categoryGroupSelectionGroup.Count(x => x.SourceID == 3).ToString(),
                            categoryGroupSelectionGroup.Count(x => x.SourceID == 4).ToString()
                        });
                    }
                }
                else
                {
                    //prducts
                    foreach (var productGroup in categoryGroup.GroupBy(x => new { name = x.Product ?? "Other", val = x.ProductID }))
                    {
                        csv.Rows.Add(new[] { 
                            categoryGroup.Key.name, 
                            productGroup.Key.name, 
                            "", 
                            "", 
                            productGroup.Count().ToString(), 
                            productGroup.Sum(x => x.Amount).ToString(), 
                            productGroup.Count(x => x.SourceID == 1).ToString(), 
                            productGroup.Count(x => x.SourceID == 2).ToString(), 
                            productGroup.Count(x => x.SourceID == 3).ToString(), 
                            productGroup.Count(x => x.SourceID == 4).ToString() 
                        });
                        //start product group summary
                                            
                       var productGroupEnquires = productGroup.Where(x => x.QueryTypeID == (byte)Spotless.Data.Query.QueryTypes.Enquiry);
                       var productGroupComplaints = productGroup.Where(x => x.QueryTypeID == (byte)Spotless.Data.Query.QueryTypes.Complaint);
                    

                        //categoryGroup.Key.name, 
                        //        productGroup.Key.name, 
                        //        varientGroup.Key.name, 
                        //        "", 
                        //Enquiry Group Total
                        csv.Rows.Add(new[] { 
                                "",//categoryGroup.Key.name, 
                                "",//productGroup.Key.name, 
                                "", 
                                "Enquiry Total", 
                                productGroupEnquires.Count().ToString(), 
                                productGroupEnquires.Sum(x => x.Amount).ToString(), 
                                productGroupEnquires.Count(x => x.SourceID == 1).ToString(), 
                                productGroupEnquires.Count(x => x.SourceID == 2).ToString(), 
                                productGroupEnquires.Count(x => x.SourceID == 3).ToString(), 
                                productGroupEnquires.Count(x => x.SourceID == 4).ToString() 
                            });
                        //Complaints Group Total
                        csv.Rows.Add(new[] { 
                                "",//categoryGroup.Key.name, 
                                "",//productGroup.Key.name, 
                                "", 
                                "Complaints Total", 
                                productGroupComplaints.Count().ToString(), 
                                productGroupComplaints.Sum(x => x.Amount).ToString(), 
                                productGroupComplaints.Count(x => x.SourceID == 1).ToString(), 
                                productGroupComplaints.Count(x => x.SourceID == 2).ToString(), 
                                productGroupComplaints.Count(x => x.SourceID == 3).ToString(), 
                                productGroupComplaints.Count(x => x.SourceID == 4).ToString() 
                            });

                        //Gropup compalint detail
                        foreach (var productGroupSelectionGroup in productGroupComplaints.GroupBy(x => new { name = x.Selection, val = x.SelectionID }))
                        {
                            csv.Rows.Add(new[] { 
                            "",//categoryGroup.Key.name,
                            "",//productGroup.Key.name,
                            "", 
                            productGroupSelectionGroup.Key.name, 
                            productGroupSelectionGroup.Count().ToString(),
                            productGroupSelectionGroup.Sum(x => x.Amount).ToString(),
                            productGroupSelectionGroup.Count(x => x.SourceID == 1).ToString(),
                            productGroupSelectionGroup.Count(x => x.SourceID == 2).ToString(),
                            productGroupSelectionGroup.Count(x => x.SourceID == 3).ToString(),
                            productGroupSelectionGroup.Count(x => x.SourceID == 4).ToString()
                        
                            });

                        }
                        
                        foreach (var varientGroup in productGroup.GroupBy(x => new { name = x.Variation ?? "Other", val = x.VariationID }))
                        {
                            csv.Rows.Add(new[] { 
                                "",//categoryGroup.Key.name, 
                                "",//productGroup.Key.name, 
                                varientGroup.Key.name, 
                                "Total", 
                                varientGroup.Count().ToString(), 
                                varientGroup.Sum(x => x.Amount).ToString(), 
                                varientGroup.Count(x => x.SourceID == 1).ToString(), 
                                varientGroup.Count(x => x.SourceID == 2).ToString(), 
                                varientGroup.Count(x => x.SourceID == 3).ToString(), 
                                varientGroup.Count(x => x.SourceID == 4).ToString() 
                            });

                            var enquires = varientGroup.Where(x => x.QueryTypeID == (byte)Spotless.Data.Query.QueryTypes.Enquiry);
                            var complaints = varientGroup.Where(x => x.QueryTypeID == (byte)Spotless.Data.Query.QueryTypes.Complaint);
                            
                            csv.Rows.Add(new[] { 
                                "",//categoryGroup.Key.name, 
                                "",//productGroup.Key.name, 
                                "",//varientGroup.Key.name, 
                                "Enquiries Total", 
                                enquires.Count().ToString(), 
                                enquires.Sum(x => x.Amount).ToString(), 
                                enquires.Count(x => x.SourceID == 1).ToString(), 
                                enquires.Count(x => x.SourceID == 2).ToString(), 
                                enquires.Count(x => x.SourceID == 3).ToString(),
                                enquires.Count(x => x.SourceID == 4).ToString()
                            });
                            csv.Rows.Add(new[] { 
                                "",//categoryGroup.Key.name, 
                                "",//productGroup.Key.name, 
                                "",//varientGroup.Key.name, 
                                "Complaints Total", 
                                complaints.Count().ToString(), 
                                complaints.Sum(x => x.Amount).ToString(), 
                                complaints.Count(x => x.SourceID == 1).ToString(), 
                                complaints.Count(x => x.SourceID == 2).ToString(), 
                                complaints.Count(x => x.SourceID == 3).ToString(), 
                                complaints.Count(x => x.SourceID == 4).ToString() 
                            });
                            foreach (var selectionGroup in complaints.GroupBy(x => new { name = x.Selection ?? "Other", val = x.SelectionID }))
                            {
                                csv.Rows.Add(new[] { 
                                    "",//categoryGroup.Key.name, 
                                    "",//productGroup.Key.name, 
                                    "",//varientGroup.Key.name, 
                                    selectionGroup.Key.name, 
                                    selectionGroup.Count().ToString(), 
                                    selectionGroup.Sum(x => x.Amount).ToString(), 
                                    selectionGroup.Count(x => x.SourceID == 1).ToString(), 
                                    selectionGroup.Count(x => x.SourceID == 2).ToString(), 
                                    selectionGroup.Count(x => x.SourceID == 3).ToString(),
                                    selectionGroup.Count(x => x.SourceID == 4).ToString()
                                });
                            }
                        }
                    }
                }

            }

            return csv;
        }


        [Authorize(Roles = "Admin, EnquiryManager, EnquiryCreator")]
        public ActionResult ComplaintsAlert()
        {
            var data = new ComplaintsAlertViewModel();
            
            //data.Categories = new Dictionary<Category, List<DrillDown>>();
            data.Categories= new Dictionary<Category,Dictionary<DrillDown,List<DrillDown>>>();
            
            foreach (var category in this.QueryService.GetCategories())
            {
                var products = QueryService.GetTopLevelDrillDown(category.CategoryId, Spotless.Data.DrillDown.DrillDownTypes.Product);
                
                var val = new Dictionary<DrillDown, List<DrillDown>>();
                foreach(var product in products){
                    var variants = QueryService.GetDrillDownsByParentId(product.DrillDownId, Spotless.Data.DrillDown.DrillDownTypes.Variation);
                    val.Add(product, variants);
                }
                
                data.Categories.Add(category, val);
            }


            //data.Categories = this.QueryService.GetCategories().ToDictionary(
            //        x => x,
            //        x => this.QueryService.GetTopLevelDrillDown(
            //            x.CategoryId,
            //        Spotless.Data.DrillDown.DrillDownTypes.Product
            //    )
            //);
            
            data.ComplaintAlerts = this.QueryService.ComplaintAlert_GetAllForUser(this.User);
            return View(data);
        }

        [HttpPost]
        [Authorize(Roles = "Admin, EnquiryManager, EnquiryCreator")]
        public ActionResult ComplaintsAlert(List<int> variantIDs)
        {
            if(variantIDs == null)
                variantIDs = new List<int>();

            var complaintAlerts = this.QueryService.ComplaintAlert_GetAllForUser(this.User);
            var aspnetUser = this.QueryService.User_GetByLoggedIn(this.User);
            foreach (var id in variantIDs.Where(x => !complaintAlerts.Select(y => y.DrillDownId).Contains(x)))
            {
                var item = new ComplaintsAlert() { UserId = aspnetUser.UserId, DrillDownId = id};
                complaintAlerts.Add(item);
                QueryService.Insert(item);
            }
            foreach (var item in complaintAlerts.Where(x => !variantIDs.Contains(x.DrillDownId)).ToList())
            {
                QueryService.Delete(item);
                complaintAlerts.Remove(item);
            }
            QueryService.SaveChanges();
            TempData["Saved"] = true;
            return RedirectToAction("ComplaintsAlert");
        }
    
    }

}
